﻿using Microsoft.Data.Sqlite;
using SQLite;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MiniExcelLibs;
using System.Linq.Expressions;

// 基于 Microsoft.Data.Sqlite;
// 发现和 System.Data.SQLite 差异：
// 1.Microsoft使用SqliteConnection 而System使用SQLiteConnection
namespace CmccTool.KPI.Services
{
    // 查询数据 - 同步
    internal class KpiQuertHelper
    {

        // 数据库连接字符串
        private readonly string connString = $"Data Source={AppConfig.dbPath};";

        // 执行查询并返回结果集
        public DataTable QueryDataBysql(string query)
        {
            try
            {
                using var connection = new SqliteConnection(connString);
                connection.Open();

                using var command = new SqliteCommand(query, connection);
                using var reader = command.ExecuteReader();
                //var dataTable = new System.Data.DataTable();
                //dataTable.Load(reader);
                var dataTable = ReadDataTable(reader);
                return dataTable;
            }
            catch (Exception ex)
            {
                // 处理异常
                MessageBox.Show($"查询执行失败: {ex.Message}");
                return new DataTable(); // 返回空的 DataTable
            }
        }


        private DataTable ReadDataTable(SqliteDataReader reader)
        {
            var table = new DataTable();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
            }

            while (reader.Read())
            {
                var row = table.NewRow();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row[i] = reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i);
                }
                table.Rows.Add(row);
            }
            return table;
        }

        // 执行查询并输出结果集
        public void QueryDataBysql(string query, string fileName)
        {
            string fd = Path.GetDirectoryName(fileName) ?? string.Empty;
            if (!string.IsNullOrEmpty(fd)) { Directory.CreateDirectory(fd); }
            var dataTable = QueryDataBysql(query);
            // 使用 MiniExcel 导出到 Excel 文件
            if (File.Exists(fileName))
            {
                File.Delete(fileName); // 删除旧的文件
            }
            MiniExcel.SaveAs(fileName, dataTable);

        }

        // 连接上运行的SQLite数据库，并执行SQL语句
        public static void SqliteRunSql(SqliteConnection connection, string sql) //SQLiteConnection
        {
            // 正确创建命令对象
            using var command = new SqliteCommand(sql, connection);
            command.ExecuteNonQuery(); // 执行命令
        }
    }
}
